import type { Router } from "express";
import type { Ioracle, IcallbackFn } from "../../types";
import { decrypt } from "../../utils/privateKey";
import { BindParameters } from "oracledb";
import { sendMail } from "./mail";

interface Header {
  engineer_badge: string;
  manager_badge: string;
  created_by: string;
  docid: string;
}

interface Data {
  unit_code: string;
  objective: string;
  teacher_badge: string;
  train_hours: string;
  trian_instructoin: string;
  start_date: string;
  end_date: string;
  assessment_method: string;
}

const SchduleRouter = (
  router: Router,
  oracle: Ioracle,
  callbackFn: IcallbackFn
) => {
  router.post("/getdept", async (_req, res) => {
    const { username } = _req.body;

    let sql = `select count(*) count from xxcc_new_hrsystem_groupmenber where groupid = 2 and menber = '${decrypt(
      username
    )}'`;
    try {
      const resultPower = await oracle.connHrm({ sql });
      if (resultPower.data[0].count === 0) {
        let sql = ` select xxcc_new_engineer_getuserdept('${decrypt(
          username.trim()
        )}') dept from dual `;
        const result = await oracle.connHrm({ sql });
        if (result.data[0].dept !== "") {
          const dataArr = result.data[0].dept.toString().split(";");
          const returnArr = [];
          for (let i = 0; i < dataArr.length; i++) {
            returnArr.push({
              code: dataArr[i],
            });
          }
          res.send({ code: 200, message: "success", data: returnArr });
        } else {
          res.send({ code: 0, message: "没有查询到数据" });
        }
      }
      else{
        sql = " select code from department where status = 'N' and org_code in ('CC','CF','CH') order by code"
        callbackFn(res,oracle.connHrm,{sql})
      }
    } catch (err) {
      res.send({ code: 0, message: err });
    }
  });

  router.post("/menu", (_req, res) => {
    const { username } = _req.body;
    let sql = ` select * from xxcc_new_hrsystem_power_v where power_user = '${decrypt(
      username.trim()
    )}' order by parent_code,menu_code `;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/engineerinfo", async (_req, res) => {
    const { dept } = _req.body;
    let sqlEngineer = ` select name_in_chinese,no,to_char(hire_date,'yyyy-mm-dd')in_date from personnel_basic where dept_code = '${dept}' and resign_code is null and dl_idl = 'I'`;
    let sqlManager = ` select b.chinese_name, b.work_num,b.dept from department a,user_info_n b where a.boss_account = b.account and code = '${dept}'`;
    try {
      const result1 = await oracle.connHrm({ sql: sqlEngineer });
      if (result1.data.length > 0) {
        try {
          const result2 = await oracle.connHrm({ sql: sqlManager });
          res.send({
            code: 200,
            message: "success",
            data: { engineer: result1.data, manager: result2.data },
          });
        } catch (err) {
          res.send({ code: 0, message: err });
        }
      } else {
        res.send({ code: 0, message: "该部门下没有工程师" });
      }
    } catch (err) {
      res.send({ code: 0, message: err });
    }
  });

  router.post("/engineercourse", (req, res) => {
    const { dept } = req.body;
    let sql = ` select unit_code,unit_desc,train_hour,train_instruction from xxcc_new_engineer_trainlist where dept_code = '${dept}' `;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/getteacher", (req, res) => {
    const { dept } = req.body;
    let sql = `select distinct work_num,chinese_name from user_info_n where dept = '${dept}' and job_status = 'employed'`;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/apply", async (req, res) => {
    let { header, data } = req.body;
    const headerT: Header = JSON.parse(header);
    const dataArr: Array<Data> = [];
    JSON.parse(data).forEach((element: Data) => {
      dataArr.push(element);
    });
    let resultDoc, resultFlow;
    try {
      let sql = "";
      let docid = "";
      if (headerT.docid) {
        sql = ` delete from xxcc_new_engineercourse_lines where docid = '${headerT.docid}'`;
        await oracle.connHrm({ sql });
        docid = headerT.docid;
      } else {
        sql =
          "select xxcc_new_engineer_docid_f(to_char(sysdate,'YYYYMMDD'))docid from dual";
        resultDoc = await oracle.connHrm({ sql });
        docid = resultDoc.data[0].docid as string;

        sql = ` insert into xxcc_new_engineercourse_header (docid,engineer_dept,engineer_badge,engineer_indate,manager_dept,manager_badge
        ,created_date,created_by,current_flow,last_update_by,last_update_date,engineer_name,manager_name,status) values 
        ( '${docid}',(select dept_code from personnel_basic where no = '${
          headerT.engineer_badge
        }' ) ,'${headerT.engineer_badge}' 
         ,(select hire_date from personnel_basic where no = '${
           headerT.engineer_badge
         }'),(select dept from user_info_n where work_num = '${
          headerT.manager_badge
        }') 
         ,'${headerT.manager_badge}',sysdate,'${decrypt(
          headerT.created_by
        )}',20,'${decrypt(headerT.created_by)}',sysdate
         ,(select name_in_chinese from personnel_basic where no = '${
           headerT.engineer_badge
         }'),(select chinese_name from user_info_n where work_num = '${
          headerT.manager_badge
        }'),'Y')`;
        await oracle.connHrm({ sql });
      }

      sql = ` insert into xxcc_new_engineercourse_lines (docid,unit_code,objective,teacher_badge,teacher_dept,teacher_name,train_hours
          ,trian_instructoin,start_date,end_date,assessment_method) values(:docid,:unit_code,:objective,:teacher_badge
          ,:teacher_dept,:teacher_name,:train_hours,:trian_instructoin,to_date(:start_date,'yyyy/mm/dd'),to_date(:end_date,'yyyy/mm/dd'),:assessment_method)  `;
      let bind: Array<BindParameters> = [];
      for (let i = 0; i < dataArr.length; i++) {
        bind.push({ docid: docid, ...dataArr[i] });
      }
      await oracle.connHrm({ sql, isMulti: true, params: bind });
      sql = `begin xxcc_new_engineer_approve('apply','${decrypt(
        headerT.created_by
      )}','${docid}',''); end;`;
      resultFlow = await oracle.connHrm({ sql });
      if (resultFlow.code === 200) {
        try {
          sql = `select  c.chinese_name,substr(c.account,9) || '@compeq.huizhou.gd.cn' email  
              from xxcc_new_engineercourse_header a,xxcc_new_engineercourse_flow b,user_info_n c
              where a.docid = b.docid and a.current_flow = b.flow and b.action_klm = c.account and a.docid = '${docid}'`;
          const resultEmail = await oracle.connHrm({ sql });
          const chinese_name = resultEmail.data[0].chinese_name as string;
          const email = resultEmail.data[0].email as string;
          await sendMail({
            mailTo: email,
            mailSubject: "新進工程師課級訓練系統-簽核通知郵件",
            mailHtml: "./mail/newengineer/send_approve.ejs",
            mailData: { chinese_name, email },
          });
          res.send({ code: 200, data: [], message: "success" });
        } catch (err) {
          res.send({
            code: 0,
            message: "申請完成，郵件發送異常。請聯繫管理員或手動發送郵件",
          });
        }
      }
    } catch (err) {
      res.send({ code: 0, message: err });
    }
  });

  return router;
};

export default SchduleRouter;
